package statics;

import java.sql.Connection;
import java.util.Vector;

import org.apache.log4j.Logger;

import batch.common.db.ConnectionManager;
import batch.common.db.ExecQuery;
import batch.common.report.LogWriter;
import batch.common.util.Utils;


public class MemberStatistics_uan {

    // �α����� ����
	String startTime = null;
	String endTime = null;
	String defaultTime = null;
	boolean bResult = false;

    public MemberStatistics(String strDate) {
        this.defaultTime = strDate;
    }

    private void exec() {
    	boolean bRe = false;
    	LogWriter.setFistStart("MemberStatistics.class", "MemberStatistics");
		LogWriter.setStart();
		
		startTime = Utils.getLocalDateTime();
		bRe = select_insert();
		endTime = Utils.getLocalDateTime();
		
		LogWriter.setEnd();
		LogWriter.setStatStart();
		LogWriter.setStatTime(startTime, endTime);
		LogWriter.setStatEnd();
		if(bRe){
			LogWriter.setLog("SUCCESS");
		}else{
			LogWriter.setLog("FAIL");
		}
		
    }


    private boolean select_insert() {
        Connection conn = null;

        String strQuery = null;
        Vector vcTable = null;

        int nSiteCount = 0;
        String countryName = null;
 
        try {
            conn = ConnectionManager.getConnection();
            ExecQuery eq = new ExecQuery();

System.out.println("DATA Start...!!");
            
            // renewal start
            // G_HIS_TOTAL
            LogWriter.setLog("G_HIS_TOTAL Renewal Insert START");
            strQuery =	"	INSERT INTO g_his_total 																												\n"+
			            "		SELECT																																																																																	\n"+
			            "  		A.REG_COUNTRY                                                                                                                                                         \n"+
			            "  		,'"+defaultTime+"' CREATE_YMD                                                                                                                                                \n"+
			            "  		, A.JOIN_FRIEND                                                                                                                                                       \n"+
			            "  		, A.JOIN_MEMBER                                                                                                                                                       \n"+
			            "  		, C.SAMSUNG_SERIAL                                                                                                                                                    \n"+
			            "  		, D.SAMSUNG_NONE_SERIAL                                                                                                                                               \n"+
			            "  		, E.LOGIN1, F.LOGIN2, G.LOGIN3                                                                                                                                        \n"+
			            "  		, B.NO_PHONE                                                                                                                                                          \n"+
			            "  		, A.MALE, A.FEMALE                                                                                                                                                    \n"+
			            "  		, H.AGE0_18, H.AGE19_25, H.AGE26_34, H.AGE35_44, H.AGE45_MAX                                                                                                          \n"+
			            "  		, B.FRIEND_TO_MEMBER                                                                                                                                                  \n"+
			            "  		, A.WITHDRAWN_MEMBER                                                                                                                                                  \n"+
			            "  		, A.WAP_TO_WEB                                                                                                                                                        \n"+
			            "  		, A.JOIN_WAP                                                                                                                                                          \n"+
			            "  		, B.CHANGE_PHONE                                                                                                                                                      \n"+
			            "  		, B.SAMSUNG_TO_SAMSUNG                                                                                                                                                \n"+
			            "  		, B.SAMSUNG_TO_OTHER                                                                                                                                                  \n"+
			            "  		, A.GENDER_NA                                                                                                                                                         \n"+
			            "  		, H.AGE_NA                                                                                                                                                            \n"+
			            "  		, C.lang                                                                                                                                                              \n"+
			            "  		, E.LOGIN1 + F.LOGIN2 + G.LOGIN3 + I.LOGIN_SUM1 LOGIN_SUM1                                                                                                            \n"+
			            "  	FROM                                                                                                                                                                    \n"+
			            "  	  	(                                                                                                                                                                   \n"+
			            "  	  	SELECT  c.name REG_COUNTRY ,c.lang                                                                                                                                  \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, JOIN_FRIEND, 0)) JOIN_FRIEND                                                                                                 \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, JOIN_MEMBER, 0)) JOIN_MEMBER                                                                                                 \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, MALE, 0)) MALE                                                                                                               \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, FEMALE, 0)) FEMALE                                                                                                           \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, GENDER_NA, 0)) GENDER_NA                                                                                                     \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, WITHDRAWN_MEMBER, 0)) WITHDRAWN_MEMBER                                                                                       \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, WAP_TO_WEB, 0)) WAP_TO_WEB                                                                                                   \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, JOIN_WAP, 0)) JOIN_WAP                                                                                                       \n"+
			            "  		FROM g_co_code_view c,                                                                                                                                                \n"+
			            "  		     (SELECT reg_country, language,                                                                                                                                   \n"+
			            "  		      	SUM(DECODE(his_cd,'1002002',cnt,0)) JOIN_FRIEND,                                                                                                              \n"+
			            "  		      	SUM(DECODE(his_cd,'1002001',cnt,0)) JOIN_MEMBER,                                                                                                              \n"+
			            "  		      	SUM(DECODE(his_cd,'1002001',DECODE(gender,'M',cnt,0),'1002002',DECODE(gender,'M',cnt,0),'1002005',DECODE(gender,'M',cnt,0),0)) MALE,                          \n"+
			            "  		      	SUM(DECODE(his_cd,'1002001',DECODE(gender,'F',cnt,0),'1002002',DECODE(gender,'F',cnt,0),'1002005',DECODE(gender,'F',cnt,0),0)) FEMALE,                        \n"+
			            "  		      	SUM(DECODE(his_cd,'1002001',DECODE(gender,'M',0,'F',0,cnt),'1002002',DECODE(gender,'M',0,'F',0,cnt),'1002005',DECODE(gender,'M',0,'F',0,cnt),0)) GENDER_NA,   \n"+
			            "  		      	SUM(DECODE(his_cd,'1002007',cnt,0)) WITHDRAWN_MEMBER,                                                                                                         \n"+
			            "  		      	SUM(DECODE(his_cd,'1002003',cnt,0)) WAP_TO_WEB,                                                                                                               \n"+
			            "  		      	SUM(DECODE(his_cd,'1002005',cnt,0)) JOIN_WAP                                                                                                                  \n"+
			            "  			FROM                                                                                                                                                                \n"+
			            "  				(SELECT m.reg_country, decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language)as language, z.his_cd, z.gender, count(*) cnt                                 \n"+
			            "  		     	FROM g_mb_member_history z, g_mb_member m                                                                                                                       \n"+
			            "  		     	WHERE z.user_no = m.user_no                                                                                                                                     \n"+
			            "  				AND z.his_cd in ('1002001','1002002','1002005','1002003','1002007')                                                                                               \n"+
			            "  				AND m.created>=to_date('"+defaultTime+"','yyyymmdd') AND m.created<to_date('"+defaultTime+"','yyyymmdd')+1                                                                      \n"+
			            "  		     	GROUP BY m.reg_country,decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language),  z.his_cd, z.gender)                                                      \n"+
			            "  			GROUP BY reg_country,language                                                                                                                                       \n"+
			            "  			) a                                                                                                                                                                 \n"+
			            "  		WHERE c.b_code = '06' AND c.s_code NOT IN('000')                                                                                                                      \n"+
			            "  			AND c.code NOT IN('0601008')                                                                                                                                        \n"+
			            "  			AND c.name = a.reg_country(+) and  c.lang = a.language(+)                                                                                                           \n"+
			            "  		GROUP BY c.name ,c.lang                                                                                                                                               \n"+
			            "  		) A ,                                                                                                                                                                 \n"+
			            "  	  	(                                                                                                                                                                   \n"+
			            "  	  	SELECT  c.name REG_COUNTRY, c.lang                                                                                                                                  \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, NO_PHONE, 0)) NO_PHONE                                                                                                       \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, FRIEND_TO_MEMBER, 0)) FRIEND_TO_MEMBER                                                                                       \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, CHANGE_PHONE, 0)) CHANGE_PHONE                                                                                               \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, SAMSUNG_TO_SAMSUNG, 0)) SAMSUNG_TO_SAMSUNG                                                                                   \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, SAMSUNG_TO_OTHER, 0)) SAMSUNG_TO_OTHER                                                                                       \n"+
			            "  		FROM g_co_code_view c,                                                                                                                                                \n"+
			            "  			(                                                                                                                                                                   \n"+
			            "  		   	SELECT reg_country ,language ,                                                                                                                                    \n"+
			            "  		    	SUM(DECODE(his_cd,'1002002',DECODE(hp_brand,'0103001',cnt,0),0)) NO_PHONE,                                                                                      \n"+
			            "  				SUM(DECODE(his_cd,'1003010',cnt,'1004003',cnt,0)) FRIEND_TO_MEMBER,                                                                                               \n"+
			            "  		        SUM(DECODE(his_cd,'1003010',cnt,'1004003',cnt,'1003011',cnt,'1004004',cnt,0)) CHANGE_PHONE,                                                                   \n"+
			            "  		        SUM(DECODE(his_cd,'1003011',cnt,'1004004',cnt,0)) SAMSUNG_TO_SAMSUNG,                                                                                         \n"+
			            "  		        SUM(DECODE(his_cd,'1003012',cnt,0)) SAMSUNG_TO_OTHER                                                                                                          \n"+
			            "  		   	FROM                                                                                                                                                              \n"+
			            "  		   		(SELECT m.reg_country ,decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language)as language, z.his_cd, z.hp_brand, count(*) cnt                             \n"+
			            "  		        FROM g_mb_buy_history z, g_mb_member m                                                                                                                        \n"+
			            "  		        WHERE z.user_no = m.user_no                                                                                                                                   \n"+
			            "  		        	AND z.his_cd in ('1003010','1004003','1003011','1004004','1003012','1002002')                                                                               \n"+
			            "  		            AND z.created>=to_date('"+defaultTime+"','yyyymmdd') AND z.created<to_date('"+defaultTime+"','yyyymmdd')+1                                                              \n"+
			            "  				GROUP BY m.reg_country,decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language), z.his_cd, z.hp_brand)                                                       \n"+
			            "  		   	GROUP BY reg_country  ,language                                                                                                                                   \n"+
			            "  		   	) a                                                                                                                                                               \n"+
			            "  		WHERE c.b_code = '06' AND c.s_code NOT IN('000')                                                                                                                      \n"+
			            "  			AND c.code NOT IN('0601008')                                                                                                                                        \n"+
			            "  			AND c.name = a.reg_country(+)  and  c.lang = a.language(+)                                                                                                          \n"+
			            "  		GROUP BY c.name  ,c.lang                                                                                                                                              \n"+
			            "  		) B ,                                                                                                                                                                 \n"+
			            "  	  	(                                                                                                                                                                   \n"+
			            "  	  	SELECT  c.name REG_COUNTRY     , c.lang                                                                                                                             \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, SAMSUNG_SERIAL, 0)) SAMSUNG_SERIAL                                                                                           \n"+
			            "  		FROM g_co_code_view c,                                                                                                                                                \n"+
			            "  			(                                                                                                                                                                   \n"+
			            "  		   	SELECT                                                                                                                                                            \n"+
			            "  				reg_country,language                                                                                                                                              \n"+
			            "  		     	,SUM(SAMSUNG_SERIAL) SAMSUNG_SERIAL                                                                                                                             \n"+
			            "  		    FROM                                                                                                                                                              \n"+
			            "  		    	(SELECT                                                                                                                                                         \n"+
			            "  		          	reg_country, language                                                                                                                                     \n"+
			            "  		         	,case when his_cd='1002006' then SUM(1)                                                                                                                     \n"+
			            "  		            	when reg_country='rus'AND his_cd='1002001' then SUM(1)                                                                                                  \n"+
			            "  		            	when reg_country='uan'AND his_cd='1002001' then SUM(1)                                                                                                  \n"+
			            "  		         	else SUM(0) end as SAMSUNG_SERIAL                                                                                                                           \n"+
			            "  		      	FROM                                                                                                                                                          \n"+
			            "  		      		(SELECT  distinct(z.user_no), m.reg_country ,decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language)as language, z.his_cd                             \n"+
			            "  		         	FROM g_mb_buy_history z, g_mb_member m                                                                                                                      \n"+
			            "  		         	WHERE z.user_no = m.user_no                                                                                                                                 \n"+
			            "  		         		AND z.his_cd in ('1002006', '1002001')                                                                                                                    \n"+
			            "  		         		AND m.created>=to_date('"+defaultTime+"','yyyymmdd') AND m.created<to_date('"+defaultTime+"','yyyymmdd')+1                                                              \n"+
			            "  		      		)                                                                                                                                                           \n"+
			            "  				GROUP BY reg_country, language,  his_cd                                                                                                                           \n"+
			            "  		   		)                                                                                                                                                               \n"+
			            "  			GROUP BY reg_country , language                                                                                                                                     \n"+
			            "  		  	) a                                                                                                                                                               \n"+
			            "  		WHERE c.b_code = '06' AND c.s_code NOT IN('000')                                                                                                                      \n"+
			            "  			AND c.code NOT IN('0601008')                                                                                                                                        \n"+
			            "  			AND c.name = a.reg_country(+)  and  c.lang = a.language(+)                                                                                                          \n"+
			            "  		GROUP BY c.name , c.lang                                                                                                                                              \n"+
			            "  		) C ,                                                                                                                                                                 \n"+
			            "  	  	(                                                                                                                                                                   \n"+
			            "  	  	SELECT c.name REG_COUNTRY,  c.lang                                                                                                                                  \n"+
			            "  			, SUM (DECODE (a.reg_country, c.name, 1, 0)) SAMSUNG_NONE_SERIAL                                                                                                    \n"+
			            "  		FROM g_co_code_view c,                                                                                                                                                \n"+
			            "  		    (SELECT m.reg_country, decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language)as language, m.user_no                                                        \n"+
			            "  		     FROM g_mb_member_history z, g_mb_member m                                                                                                                        \n"+
			            "  		     WHERE z.user_no = m.user_no                                                                                                                                      \n"+
			            "  		     	AND z.his_cd = '1002001'                                                                                                                                        \n"+
			            "  		     	AND m.created>=to_date('"+defaultTime+"','yyyymmdd') AND m.created<to_date('"+defaultTime+"','yyyymmdd')+1                                                                    \n"+
			            "  		  		AND m.reg_country NOT IN('rus','uan','fra')                                                                                                                     \n"+
			            "  		     MINUS                                                                                                                                                            \n"+
			            "  		    SELECT m.reg_country, decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language)as language, m.user_no                                                         \n"+
			            "  		     FROM g_mb_buy_history z, g_mb_member m                                                                                                                           \n"+
			            "  		     WHERE z.user_no = m.user_no                                                                                                                                      \n"+
			            "  		     	AND z.his_cd = '1002006'                                                                                                                                        \n"+
			            "  		     	AND m.created>=to_date('"+defaultTime+"','yyyymmdd') AND m.created<to_date('"+defaultTime+"','yyyymmdd')+1                                                                    \n"+
			            "  		  		AND m.reg_country NOT IN('rus','uan','fra')                                                                                                                     \n"+
			            "  		  	) a                                                                                                                                                               \n"+
			            "  		WHERE c.b_code = '06' AND c.s_code NOT IN('000')                                                                                                                      \n"+
			            "  			AND c.code NOT IN('0601008')                                                                                                                                        \n"+
			            "  			AND c.name = a.reg_country(+) and  c.lang = a.language(+)                                                                                                           \n"+
			            "  		GROUP BY c.name,c.lang                                                                                                                                                \n"+
			            "  		) D ,                                                                                                                                                                 \n"+
			            "  	  	(                                                                                                                                                                   \n"+
			            "  	  	SELECT c.name REG_COUNTRY, c.lang                                                                                                                                   \n"+
			            "  			,SUM (DECODE (a.reg_country, c.name, 1, 0)) LOGIN1                                                                                                                  \n"+
			            "  		FROM g_co_code_view c,                                                                                                                                                \n"+
			            "  		    (SELECT   l.user_no, MIN (m.reg_country) reg_country ,MIN (m.language) language                                                                                   \n"+
			            "  			FROM g_mb_login_history l, g_mb_member m                                                                                                                            \n"+
			            "  			WHERE l.user_no = m.user_no                                                                                                                                         \n"+
			            "  		    	AND l.his_cd = '1001001'                                                                                                                                        \n"+
			            "  		        AND l.create_ymdhms LIKE '"+defaultTime+"%'                                                                                                                          \n"+
			            "  			GROUP BY l.user_no                                                                                                                                                  \n"+
			            "  		    HAVING COUNT (*) = 1) a                                                                                                                                           \n"+
			            "  		WHERE c.b_code = '06' AND c.s_code NOT IN('000')                                                                                                                      \n"+
			            "  			AND c.code NOT IN('0601008')                                                                                                                                        \n"+
			            "  			AND c.name = a.reg_country(+)  and  c.lang = a.language(+)                                                                                                          \n"+
			            "  		GROUP BY c.name ,c.lang                                                                                                                                               \n"+
			            "  		) E ,                                                                                                                                                                 \n"+
			            "  	  	(                                                                                                                                                                   \n"+
			            "  	  	SELECT c.name REG_COUNTRY, c.lang                                                                                                                                   \n"+
			            "  			 ,SUM (DECODE (a.reg_country, c.name, 1, 0)) LOGIN2                                                                                                                 \n"+
			            "  		FROM g_co_code_view c,                                                                                                                                                \n"+
			            "  		    (SELECT   l.user_no,MIN (m.reg_country) reg_country ,MIN (m.language) language                                                                                    \n"+
			            "  			FROM g_mb_login_history l, g_mb_member m                                                                                                                            \n"+
			            "  		    WHERE l.user_no = m.user_no                                                                                                                                       \n"+
			            "  		    	AND l.his_cd = '1001001'                                                                                                                                        \n"+
			            "  		        AND l.create_ymdhms LIKE '"+defaultTime+"%'                                                                                                                          \n"+
			            "  			GROUP BY l.user_no                                                                                                                                                  \n"+
			            "  			HAVING COUNT (*) = 2) a                                                                                                                                             \n"+
			            "  		WHERE c.b_code = '06' AND c.s_code NOT IN('000')                                                                                                                      \n"+
			            "  			AND c.code NOT IN('0601008')                                                                                                                                        \n"+
			            "  			AND c.name = a.reg_country(+)  and  c.lang = a.language(+)                                                                                                          \n"+
			            "  		GROUP BY c.name ,c.lang                                                                                                                                               \n"+
			            "  		) F ,                                                                                                                                                                 \n"+
			            "  	  	(                                                                                                                                                                   \n"+
			            "  	  	SELECT c.name REG_COUNTRY, c.lang                                                                                                                                   \n"+
			            "  			 ,SUM (DECODE (a.reg_country, c.name, 1, 0)) LOGIN3                                                                                                                 \n"+
			            "  		FROM g_co_code_view c,                                                                                                                                                \n"+
			            "  		    (SELECT   l.user_no,MIN (m.reg_country) reg_country ,MIN (m.language) language                                                                                    \n"+
			            "  			FROM g_mb_login_history l, g_mb_member m                                                                                                                            \n"+
			            "  		    	WHERE l.user_no = m.user_no                                                                                                                                     \n"+
			            "  		        	AND l.his_cd = '1001001'                                                                                                                                    \n"+
			            "  		        AND l.create_ymdhms LIKE '"+defaultTime+"%'                                                                                                                          \n"+
			            "  			GROUP BY l.user_no                                                                                                                                                  \n"+
			            "  		    HAVING COUNT (*) >= 3) a                                                                                                                                          \n"+
			            "  		WHERE c.b_code = '06' AND c.s_code NOT IN('000')                                                                                                                      \n"+
			            "  			AND c.code NOT IN('0601008')                                                                                                                                        \n"+
			            "  			AND c.name = a.reg_country(+) and  c.lang = a.language(+)                                                                                                           \n"+
			            "  		GROUP BY c.name ,c.lang                                                                                                                                               \n"+
			            "  		) G,                                                                                                                                                                  \n"+
			            "  	  	(                                                                                                                                                                   \n"+
			            "  	  	SELECT  c.name REG_COUNTRY, c.lang                                                                                                                                  \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, age0_18, 0)) AGE0_18                                                                                                         \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, AGE19_25, 0)) AGE19_25                                                                                                       \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, AGE26_34, 0)) AGE26_34                                                                                                       \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, AGE35_44, 0)) AGE35_44                                                                                                       \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, age_max, 0)) AGE45_MAX                                                                                                       \n"+
			            "  		    ,SUM (DECODE (a.reg_country, c.name, age_na, 0)) AGE_NA                                                                                                           \n"+
			            "  		FROM g_co_code_view c,                                                                                                                                                \n"+
			            "  			(SELECT reg_country,language                                                                                                                                        \n"+
			            "  				,SUM(DECODE(age, '18', cnt,'0')) as AGE0_18                                                                                                                       \n"+
			            "  		     	,SUM(DECODE(age, '25', cnt,'0')) as AGE19_25                                                                                                                    \n"+
			            "  		     	,SUM(DECODE(age, '34', cnt,'0')) as AGE26_34                                                                                                                    \n"+
			            "  		     	,SUM(DECODE(age, '44', cnt,'0')) as AGE35_44                                                                                                                    \n"+
			            "  		     	,SUM(DECODE(age, '45', cnt,'0')) as AGE_max                                                                                                                     \n"+
			            "  		     	,SUM(DECODE(age, 'NA', cnt,'0')) as AGE_na                                                                                                                      \n"+
			            "  		    FROM                                                                                                                                                              \n"+
			            "  		    	(SELECT m.reg_country ,decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language)as language                                                                 \n"+
			            "  		       		,get_stats_age(z.birth) as age                                                                                                                              \n"+
			            "  		    		,count(1) cnt                                                                                                                                                 \n"+
			            "  		    	FROM g_mb_member_history z, g_mb_member m                                                                                                                       \n"+
			            "  		    	WHERE z.user_no = m.user_no                                                                                                                                     \n"+
			            "  					AND z.his_cd IN ('1002001','1002002','1002005')                                                                                                                 \n"+
			            "  		    		AND m.created>=to_date('"+defaultTime+"','yyyymmdd') AND m.created<to_date('"+defaultTime+"','yyyymmdd')+1                                                  \n"+
			            "  		    	GROUP BY m.reg_country, decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language),get_stats_age(z.birth)                                                    \n"+
			            "  				)                                                                                                                                                                 \n"+
			            "  			GROUP BY reg_country,language                                                                                                                                       \n"+
			            "  		  	) a                                                                                                                                                               \n"+
			            "  		WHERE c.b_code = '06' AND c.s_code NOT IN('000')                                                                                                                      \n"+
			            "  			AND c.code NOT IN('0601008')                                                                                                                                        \n"+
			            "  			AND c.name = a.reg_country(+) and  c.lang = a.language(+)                                                                                                           \n"+
			            "  		GROUP BY c.name, c.lang                                                                                                                                               \n"+
			            "  		) H,                                                                                                                                                                   \n"+
			            "  	  (                                                                                                                                                                     \n"+
			            "  		SELECT reg_country, lang_cd lang, SUM(login1 + login2 + login3) login_sum1                                                                                            \n"+
			            "  		  FROM g_his_total                                                                                                                                                    \n"+
			            "  		 WHERE create_ymd > to_char(TRUNC(add_months(to_date('"+defaultTime+"', 'YYYYMMDD'), -1)), 'YYYYMMDD')                                                                \n"+
			            "  		                      AND create_ymd < '"+defaultTime+"'                                                                                                              \n"+
			            "  		 GROUP BY reg_country, lang_cd                                                                                                                                        \n"+
			            "  		) I                                                                                                                                                                   \n"+ 
			            "  	WHERE A.REG_COUNTRY = B.REG_COUNTRY AND  A.lang = B.lang                                                                                                                \n"+
			            "  		AND B.REG_COUNTRY = C.REG_COUNTRY    AND B.lang = C.lang                                                                                                              \n"+
			            "  		AND C.REG_COUNTRY = D.REG_COUNTRY    AND C.lang = D.lang                                                                                                              \n"+
			            "  		AND D.REG_COUNTRY = E.REG_COUNTRY    AND D.lang = E.lang                                                                                                              \n"+
			            "  		AND E.REG_COUNTRY = F.REG_COUNTRY     AND E.lang = F.lang                                                                                                             \n"+
			            "  		AND F.REG_COUNTRY = G.REG_COUNTRY     AND F.lang = G.lang                                                                                                             \n"+
			            "  		AND G.REG_COUNTRY = H.REG_COUNTRY    AND G.lang = H.lang                                                                                                              \n"+			            
			            "  		AND H.REG_COUNTRY = I.REG_COUNTRY AND H.lang = I.lang                                                                                                                 ";			            
 		
            eq.exec(conn, strQuery);
	        LogWriter.setLog("G_HIS_TOTAL Renewal Insert END");

	        
System.out.println("================ Middle ===========================");	        
	        
	        // G_HIS_OPTIN
	        LogWriter.setLog("G_HIS_OPTIN Renewal Insert START");
            strQuery =  "INSERT INTO g_his_optin \n"+
			            " SELECT c.name reg_country ,																																															\n"+
			            " 	'"+defaultTime+"' create_ymd,                                                                                         \n"+
			            " 	a.code optin_code,                                                                                                    \n"+
			            " 	SUM (DECODE (a.reg_country, c.name, a.cnt, 0)) optin_code_count,                                                      \n"+
			            " 	a.language LANG_CD                                                                                                    \n"+
			            " FROM g_co_code_view c,                                                                                                  \n"+
			            " 	(                                                                                                                     \n"+
			            " 	SELECT m.reg_country,decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language)as language, z.code, COUNT(*) cnt   \n"+
			            "     FROM g_mb_optin_history z, g_mb_member m                                                                            \n"+
			            " 	WHERE z.user_no = m.user_no                                                                                           \n"+
			            "     	AND z.his_cd IN ('1002001', '1002002','1002005')                                                                  \n"+
			            "         AND z.status_cd = 'Y'                                                                                           \n"+
			            "         AND m.created>=to_date('"+defaultTime+"','yyyymmdd')                                                            \n"+
			            "         AND m.created<to_date('"+defaultTime+"','yyyymmdd')+1                                                           \n"+
			            " 	GROUP BY m.reg_country,decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language), z.code                          \n"+
			            " 	) a                                                                                                                   \n"+
			            " WHERE c.b_code = '06' AND c.s_code NOT IN('000')                                                                        \n"+
			            " 	AND c.code NOT IN('0601008')                                                                                          \n"+
			            " 	AND c.name = a.reg_country and  c.lang = a.language                                                                   \n"+
			            " GROUP BY c.name, a.code, a.language                                                                                     ";

            eq.exec(conn, strQuery);
            
            strQuery =  "INSERT INTO g_his_optin 																	\n"+
			            " SELECT   c.name reg_country																																					\n"+
			            " 	,'"+defaultTime+"' created                                                                        \n"+
			            " 	,'0000000'  optin_code                                                                            \n"+
			            " 	,SUM(optin_out_cnt) optout_count                                                                  \n"+
			            " 	,d.language LANG_CD                                                                               \n"+
			            " FROM g_co_code_view c, (                                                                            \n"+
			            " 	SELECT  reg_country, language                                                                     \n"+
			            " 		,SUM(get_optin_out(reg_country,user_no)) optin_out_cnt                                          \n"+
			            " 	FROM (                                                                                            \n"+
			            " 		SELECT count(z.STATUS_CD) optin_code_count                                                      \n"+
			            " 			,m.reg_country                                                                                \n"+
			            " 			,decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language)as language                     \n"+
			            " 			,z.user_no                                                                                    \n"+
			            " 		FROM  g_mb_optin_history z, g_mb_member m                                                       \n"+
			            " 		WHERE z.user_no = m.user_no                                                                     \n"+
			            "   			AND z.his_cd IN ('1002001', '1002002','1002005')                                            \n"+
			            "       		AND m.created>=to_date('"+defaultTime+"','yyyymmdd')                                      \n"+
			            "       		AND m.created<to_date('"+defaultTime+"','yyyymmdd')+1                                     \n"+
			            " 			AND z.status_cd='N'                                                                           \n"+
			            " 		GROUP BY m.reg_country,decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language), z.user_no \n"+
			            " 	) GROUP BY reg_country,language) d                                                                \n"+
			            " WHERE c.b_code = '06' AND c.s_code NOT IN('000')                                                    \n"+
			            " 	AND c.code NOT IN('0601008')                                                                      \n"+
			            " 	AND c.name = d.reg_country                                                                        \n"+
			            " 	AND c.lang = d.language                                                                           \n"+
			            " GROUP BY c.name,d.language                                                                          ";
            
			eq.exec(conn, strQuery);
			
            LogWriter.setLog("G_HIS_OPTIN Renewal Insert END");
            
            // G_HIS_OPTIN_TO_OPTOUT
            LogWriter.setLog("G_HIS_OPTIN_TO_OPTOUT Renewal Insert START");
            strQuery =  "	INSERT INTO g_his_optin_to_optout														\n"+
			            " SELECT  c.name reg_country																																					\n"+
			            " 	,'"+defaultTime+"' create_ymd                                                                     \n"+
			            " 	,d.code optin_code                                                                                \n"+
			            " 	,SUM(DECODE (d.reg_country, c.name, d.optin_code_count, 0)) OPTIN_CODE_COUNT                      \n"+
			            " 	,d.language LANG_CD                                                                               \n"+
			            " FROM g_co_code_view c,                                                                              \n"+
			            " 	(SELECT reg_country, language                                                                     \n"+
			            " 		,code                                                                                           \n"+
			            "        	,sum(optin_code_count) optin_code_count                                                     \n"+
			            "    	FROM                                                                                            \n"+
			            "    		(SELECT b.reg_country                                                                         \n"+
			            " 	   		,decode(b.language,'',GET_LANG_NULL(b.reg_country),b.language) as language                  \n"+
			            "         	,a.code                                                                                   \n"+
			            "         	,count(a.STATUS_CD) optin_code_count                                                      \n"+
			            "         FROM  g_mb_optin_history a, g_mb_member b                                                   \n"+
			            " 		WHERE a.create_ymdhms LIKE '"+defaultTime+"%'                                                   \n"+
			            "        		AND a.status_cd='N'                                                                       \n"+
			            "        		AND a.user_no = b.user_no                                                                 \n"+
			            " 		GROUP BY b.reg_country,decode(b.language,'',GET_LANG_NULL(b.reg_country),b.language), a.code)   \n"+
			            " 	GROUP BY reg_country,language, code) d                                                            \n"+
			            " WHERE c.b_code = '06' AND c.s_code NOT IN('000')                                                    \n"+
			            " 	AND c.code NOT IN('0601008')                                                                      \n"+
			            " 	AND c.name = d.reg_country                                                                        \n"+
			            " 	AND c.lang = d.language                                                                           \n"+
			            " GROUP BY c.name, d.code,d.language                                                                  ";
            
            eq.exec(conn, strQuery);
            LogWriter.setLog("G_HIS_OPTIN_TO_OPTOUT Renewal Insert END");
            
            /*
            // G_HIS_TOP5
            LogWriter.setLog("G_HIS_TOP5 Renewal Insert START");
            strQuery =  "INSERT INTO g_his_top5 																			\n"+
			            " SELECT name reg_country, '"+defaultTime+"' ,hp_model,cnt,rank1,rank2,sn,language as LANG_CD																																																					\n"+
			            " FROM (SELECT c.name, a.hp_model                                                                                                                                                                     \n"+
			            "     ,SUM (DECODE (a.reg_country, c.name, a.cnt, 0)) cnt                                                                                                                                             \n"+
			            "     ,SUM (DECODE (a.reg_country, c.name, a.rank1, 0)) rank1                                                                                                                                         \n"+
			            "     ,SUM (DECODE (a.reg_country, c.name, a.rank2, 0)) rank2                                                                                                                                         \n"+
			            "     ,SUM (DECODE (a.reg_country, c.name, a.sn, 0)) sn                                                                                                                                               \n"+
			            "     ,a.language                                                                                                                                                                                     \n"+
			            "       FROM g_co_code_view c,                                                                                                                                                                        \n"+
			            "            (                                                                                                                                                                                        \n"+
			            "   select reg_country,language, created, hp_model, cnt, rank1, rank2, sn from (                                                                                                                      \n"+
			            "    select site as reg_country,language, created, hp_model, cnt, rank1, rank2, sn                                                                                                                    \n"+
			            "    from (select created, site,language, hp_model, cnt,                                                                                                                                              \n"+
			            "                 rank() over (partition by created, site,language, sn order by cnt desc) rank1,                                                                                                      \n"+
			            "                 rank() over (partition by created, site,language, sn order by cnt desc) rank2,                                                                                                      \n"+
			            "                 sn                                                                                                                                                                                  \n"+
			            "          from (select created, site,language, hp_model, decode(his_cd,'1002001',decode(site,'rus',2,'uan',2,1),'1002006',2,'1002005',3,0) sn, count(*) cnt, grouping(hp_model) m                    \n"+
			            "                from (select created, site, language, hp_model, his_cd                                                                                                                               \n"+
			            "                      from (select '20060522' created, m.reg_country site, decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language) as language, z.hp_model, z.his_cd,                          \n"+
			            "                                   row_number() over (partition by m.reg_country,decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language), z.hp_model, m.user_no order by z.his_cd desc) rnum   \n"+
			            "                            from g_mb_buy_history z, g_mb_member m                                                                                                                                   \n"+
			            "                            where z.user_no=m.user_no                                                                                                                                                \n"+
			            "                                  and m.created>=to_date('"+defaultTime+"','yyyymmdd') and m.created<to_date('"+defaultTime+"','yyyymmdd')+1                                                         \n"+
			            "                                  and z.his_cd in ('1002001','1002005', '1002006')                                                                                                                   \n"+
			            "                                  and z.hp_model is not null                                                                                                                                         \n"+
			            "                                  and z.hp_model <> 'WAP_TEMP'                                                                                                                                       \n"+
			            "                                  and z.hp_model <> 'SIM_TEMP')                                                                                                                                      \n"+
			            "                      where rnum=1)                                                                                                                                                                  \n"+
			            "                group by created, site,language, rollup(hp_model, his_cd))                                                                                                                           \n"+
			            "          where m=0)                                                                                                                                                                                 \n"+
			            "    where rank2 <= 5                                                                                                                                                                                 \n"+
			            "    )                                                                                                                                                                                                \n"+
			            "           ) a                                                                                                                                                                                       \n"+
			            "       WHERE c.b_code = '06' AND c.s_code NOT IN('000') AND c.code NOT IN('0601008')                                                                                                                 \n"+
			            " 	AND c.name = a.reg_country                                                                                                                                                                        \n"+
			            " 	AND c.lang = a.language                                                                                                                                                                           \n"+
			            " GROUP BY c.name,a.language, a.hp_model, a.sn                                                                                                                                                        \n"+
			            " ) WHERE cnt <> 0                                                                                                                                                                                    ";
            
            eq.exec(conn, strQuery);
            LogWriter.setLog("G_HIS_TOP5 Renewal Insert END");
            */


            // G_HIS_BRAND
            LogWriter.setLog("G_HIS_BRAND Renewal Insert START");
            strQuery =  " INSERT INTO g_his_brand																																																			\n"+
			            " SELECT name reg_country,'"+defaultTime+"' create_ymd,decode(hp_brand,'','0103NA',hp_brand)  as hp_brand,total_count, language as LANG_CD  \n"+
			            " FROM (SELECT c.name,a.language, a.hp_brand, SUM (DECODE (a.reg_country, c.name, a.cnt, 0)) total_count                                    \n"+
			            "        FROM g_co_code_view c,                                                                                                             \n"+
			            "             (                                                                                                                             \n"+
			            " 			select reg_country,language,hp_brand, COUNT (*) cnt                                                                                 \n"+
			            " 			from (                                                                                                                              \n"+
			            " 				SELECT  distinct(z.user_no),m.reg_country,decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language) as language, z.hp_brand   \n"+
			            " 				FROM g_mb_buy_history z, g_mb_member m                                                                                            \n"+
			            " 				WHERE z.user_no = m.user_no                                                                                                       \n"+
			            " 				AND z.his_cd in ('1002001','1002002','1002005','1002006')                                                                         \n"+
			            " 				AND m.created >= TO_DATE('"+defaultTime+"', 'YYYYMMDD') AND m.created < TO_DATE('"+defaultTime+"', 'YYYYMMDD')+1                  \n"+
			            " 			) GROUP BY reg_country,language,hp_brand                                                                                            \n"+
			            "            ) a                                                                                                                            \n"+
			            "     WHERE c.b_code = '06' AND c.s_code NOT IN('000') AND c.code NOT IN('0601008')                                                         \n"+
			            " 	  AND c.name = a.reg_country                                                                                                            \n"+
			            " 	  AND c.lang = a.language                                                                                                               \n"+
			            " GROUP BY c.name, a.hp_brand, a.language                                                                                                   \n"+
			            " ) WHERE total_count <> 0                                                                                                                  ";
            
            eq.exec(conn, strQuery);
            LogWriter.setLog("G_HIS_BRAND Insert Renewal END");            


            // PROVIDER SELECT -> INSERT
            LogWriter.setLog("G_HIS_PROVIDER Renewal Insert START");
            strQuery =  " INSERT INTO g_his_provider																																																				\n"+				
			            " SELECT name reg_country,'"+defaultTime+"' create_ymd,decode(hp_provider,'','0104NA',hp_provider)  as hp_provider,total_count, language as LANG_CD  \n"+
			            " FROM (SELECT c.name,a.language, a.hp_provider,SUM (DECODE (a.reg_country, c.name, a.cnt, 0)) total_count                                           \n"+
			            "        FROM g_co_code_view c,                                                                                                                      \n"+
			            "             (                                                                                                                                      \n"+
			            " 			select reg_country,language,hp_provider, COUNT (*) cnt                                                                                       \n"+
			            " 			from (                                                                                                                                       \n"+
			            " 				SELECT  distinct(z.user_no),m.reg_country,decode(m.language,'',GET_LANG_NULL(m.reg_country),m.language) as language, z.hp_provider         \n"+
			            " 				FROM g_mb_buy_history z, g_mb_member m                                                                                                     \n"+
			            " 				WHERE z.user_no = m.user_no                                                                                                                \n"+
			            " 				AND z.his_cd in ('1002001','1002002','1002005','1002006')                                                                                  \n"+
			            " 				AND m.created >= TO_DATE('"+defaultTime+"', 'YYYYMMDD') AND m.created < TO_DATE('"+defaultTime+"', 'YYYYMMDD')+1                           \n"+
			            " 			) GROUP BY reg_country,language,hp_provider                                                                                                  \n"+
			            "            ) a                                                                                                                                     \n"+
			            "     WHERE c.b_code = '06' AND c.s_code NOT IN('000') AND c.code NOT IN('0601008')                                                                  \n"+
			            "    	  AND c.name = a.reg_country                                                                                                                   \n"+
			            " 	  AND c.lang = a.language                                                                                                                        \n"+
			            " GROUP BY c.name,a.language, a.hp_provider                                                                                                          \n"+
			            " ) WHERE total_count <> 0                                                                                                                           ";
            
            eq.exec(conn, strQuery);
            LogWriter.setLog("G_HIS_PROVIDER Renewal Insert END");


System.out.println("DATA success...!!");            
            
            conn.commit();
            bResult = true;            	
        } catch (Exception e) {
        	if (conn != null) {
                try {
                    conn.rollback();
                } catch (Exception se) {}
            }
            e.printStackTrace();
            LogWriter.setErr(e.toString());
            Logger.getLogger("nohup").error(strQuery);
            bResult = false;
            batch.common.report.ReportError.byEmail("Error of MemberStatistics Error! (" + defaultTime + ") !!", e.toString());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception se) {}
            }
        }
        return bResult;
    }

    /**
     * @param args
     */
    public static void main(String[] args) {
        new MemberStatistics(args[0]).exec();
    }

}